package com.r7data.dbstruct.demo;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.PooledDataSource;

/**
 * c3p0 数据库连接池测试
 * @author admin
 *
 */
public class DbSourceTest {

	public static void showSourceInfo(ComboPooledDataSource cpds){
		
		PooledDataSource pds = (PooledDataSource) cpds;
		try {
			System.out.println("------------c3p0连接池链接状态--------------");
			System.out.println("c3p0连接池中 【 总共 】 连接数量：" + pds.getNumConnectionsDefaultUser());
			System.out.println("c3p0连接池中 【  忙  】 连接数量：" + pds.getNumBusyConnectionsDefaultUser());
			System.out.println("c3p0连接池中 【 空闲 】 连接数量：" + pds.getNumIdleConnectionsDefaultUser());
			System.out.println("c3p0连接池中 【未关闭】 连接数量：" + pds.getNumUnclosedOrphanedConnectionsAllUsers());
		} catch (SQLException e) {
			System.out.println("c3p0连接池异常！");
		}
		
	}
	
	
	public static void con1() {

		String url = "jdbc:mysql://localhost:3306/other_test";

		String driverClass = "com.mysql.jdbc.Driver";

		String dbUser = "root";
		String password = "admin";

		ComboPooledDataSource cpds = new ComboPooledDataSource(true);

		try {
			cpds.setDataSourceName("mydatasource");
			cpds.setDriverClass(driverClass);
			cpds.setJdbcUrl(url);

			cpds.setUser(dbUser);
			cpds.setPassword(password);

			cpds.setMaxPoolSize(10);
			cpds.setMinPoolSize(0);
			cpds.setAcquireIncrement(1);
			cpds.setInitialPoolSize(1);
			cpds.setMaxIdleTime(10);//最大空闲时间,超出时间则丢弃
		} catch (PropertyVetoException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		showSourceInfo(cpds);
		
		try {
			Connection con = cpds.getConnection();
			
			showSourceInfo(cpds);
			
			con.close();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		showSourceInfo(cpds);
		
		try {
			Thread.sleep(20000);
		} catch (InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		showSourceInfo(cpds);
	}
	
	
	public static void con() {

		String dbUrl = "jdbc:mysql://localhost:3306/other_test";

		String driverClass = "com.mysql.jdbc.Driver";

		String dbUser = "root";
		String dbPassword = "admin";

		ComboPooledDataSource cpds = new ComboPooledDataSource(true);

		try {
			cpds.setDataSourceName("mydatasource");
			cpds.setDriverClass(driverClass);
			cpds.setJdbcUrl(dbUrl);

			cpds.setUser(dbUser);
			cpds.setPassword(dbPassword);

			cpds.setMaxPoolSize(10);
			cpds.setMinPoolSize(5);
			cpds.setAcquireIncrement(1);
			cpds.setInitialPoolSize(1);
			cpds.setMaxIdleTime(10);//最大空闲时间,超出时间则丢弃
		} catch (PropertyVetoException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		showSourceInfo(cpds);
		
		try {
			Connection con = cpds.getConnection();
			
			showSourceInfo(cpds);
			
			con.close();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		showSourceInfo(cpds);
		
		try {
			Thread.sleep(1000);
		} catch (InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		showSourceInfo(cpds);
	}
	
	
	public static void getMysqlCon(){
		
		String dbUrl = "jdbc:mysql://localhost:3306/other_test";

		//String driverClass = "com.mysql.jdbc.Driver";
		//String driverClass = "oracle.jdbc.driver.OracleDriver";
		
		String driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		
		String dbUser = "root";
		String dbPassword = "admin";
		
		/*
		try {
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		*/
		
		
		try {
			//Driver driver = new OracleDriver();
			
			//DriverManager.registerDriver(driver);
			Class.forName(driverClass);
			
			Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
			
			String sql = "select className from `classinfo` ";
			
			PreparedStatement pst = null;
			ResultSet rst = null;
			
			pst = con.prepareStatement(sql);
			rst = pst.executeQuery();
			
			while(rst.next()){
				
				String tableName = rst.getString(1);
				
				System.out.println(tableName);
				
			}
			
			
			rst.close();
			pst.close();
			con.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void getOraclCon(){
		
		String dbUrl = "jdbc:oracle:thin:@172.16.1.100:1521/orcl";
		
		//String driverClass = "oracle.jdbc.driver.OracleDriver";
		String driverClass = "com.mysql.jdbc.Driver";
		
		String dbUser = "admin";
		String dbPassword = "admin";
		
		
		try {
			Class.forName(driverClass);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//getMysqlCon();
		
		try {
			Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
			
			String sql = "select table_name from tabs ";
			
			PreparedStatement pst = null;
			ResultSet rst = null;
			
			pst = con.prepareStatement(sql);
			rst = pst.executeQuery();
			
			while(rst.next()){
				
				String tableName = rst.getString(1);
				
				System.out.println(tableName);
				
			}
			
			
			rst.close();
			pst.close();
			con.close();
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	public static void main(String[] args) {

		getMysqlCon();
		
		System.out.println("finished ... ");
		
	}

}
